package cn.anquing.demo.mybatisplus.code.service.impl;

import cn.anquing.demo.mybatisplus.code.pojo.entity.User;
import cn.anquing.demo.mybatisplus.code.dao.UserDao;
import cn.anquing.demo.mybatisplus.code.service.IUserService;
import com.baomidou.mybatisplus.annotation.FieldFill;
import com.baomidou.mybatisplus.annotation.TableField;
import com.baomidou.mybatisplus.core.conditions.query.LambdaQueryWrapper;
import com.baomidou.mybatisplus.core.conditions.query.QueryWrapper;
import com.baomidou.mybatisplus.core.toolkit.Wrappers;
import com.baomidou.mybatisplus.core.toolkit.support.SFunction;
import com.baomidou.mybatisplus.extension.service.impl.ServiceImpl;
import io.swagger.annotations.ApiModelProperty;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import java.time.LocalDateTime;
import java.util.Arrays;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
 * <p>
 *  服务实现类
 * </p>
 *
 * @author wqq
 * @since 2022-04-27
 */
@Service
public class UserServiceImpl extends ServiceImpl<UserDao, User> implements IUserService {

    @Autowired
    private UserDao userDao;


    @Override
    public void test() {
        List<User> list = this.list();
        System.out.println(list.size());
    }

    @Override
    public List<User> eg1() {
        //1、名字中包含雨并且年龄小于40
        //name like '%雨%' and age<40

////        QueryWrapper<User> queryWrapper = Wrappers.query();
////        queryWrapper.like("name", "雨").lt("age",40);

        LambdaQueryWrapper<User> queryWrapper = Wrappers.<User>lambdaQuery().like(User::getName, "雨").lt(User::getAge, 40);
        return this.list(queryWrapper);
    }

    @Override
    public List<User> eg2() {
        //2、名字中包含雨并且龄大于等于20且小于等于40并且email不为空
        //   name like '%雨%' and age between 20 and 40 and email is not null

        LambdaQueryWrapper<User> queryWrapper = Wrappers.<User>lambdaQuery().like(User::getName, "雨").between(User::getAge, 20,40)
                .isNotNull(User::getEmail);
        return this.list(queryWrapper);
    }

    @Override
    public List<User> eg3() {
        // 3、名字为王姓或者年龄大于等于25，按照年龄降序排列，年龄相同按照id升序排列
        //   name like '王%' or age>=25 order by age desc,id asc
        LambdaQueryWrapper<User> queryWrapper = Wrappers.<User>lambdaQuery().likeRight(User::getName, "王")
                .or().ge(User::getAge, 25)
                .orderByDesc(User::getAge)
                .orderByAsc(User::getId);
        return this.list(queryWrapper);
    }

    @Override
    public List<User> eg4() {
        // 4、创建日期为2019年2月14日并且直属上级为名字为王姓
        //      date_format(create_time,'%Y-%m-%d')='2019-02-14' and manager_id in ()
        QueryWrapper<User> queryWrapper = Wrappers.query();
        queryWrapper.apply("date_format(create_time,'%Y-%m-%d')={0}", "2019-02-14")
                .inSql("manager_id","select id from user where name like '王%'");
        return this.list(queryWrapper);
    }

    @Override
    public List<User> eg5() {
        //5、名字为王姓并且（年龄小于40或邮箱不为空）
        //    name like '王%' and (age<40 or email is not null)
        LambdaQueryWrapper<User> queryWrapper = Wrappers.<User>lambdaQuery().likeRight(User::getName, "王")
                .and(query -> query.lt(User::getAge, 40).or().isNotNull(User::getEmail));
        return this.list(queryWrapper);
    }

    @Override
    public List<User> eg6() {
        //6、名字为王姓或者（年龄小于40并且年龄大于20并且邮箱不为空）
        //    name like '王%' or (age<40 and age>20 and email is not null)
        LambdaQueryWrapper<User> queryWrapper = Wrappers.<User>lambdaQuery().likeRight(User::getName, "王")
                .or(query -> query.lt(User::getAge, 40).ge(User::getAge,20).isNotNull(User::getEmail));
        return this.list(queryWrapper);
    }

    @Override
    public List<User> eg7() {
        //7、（年龄小于40或邮箱不为空）并且名字为王姓
        //    (age<40 or email is not null) and name like '王%'
        LambdaQueryWrapper<User> queryWrapper = Wrappers.<User>lambdaQuery()
                .nested(query -> query.lt(User::getAge, 40).or().isNotNull(User::getEmail))
                .likeRight(User::getName, "王");
        return this.list(queryWrapper);
    }

    @Override
    public List<User> eg8() {
        //8、年龄为30、31、34、35
        //    age in (30、31、34、35)
        LambdaQueryWrapper<User> queryWrapper = Wrappers.<User>lambdaQuery()
                .in(User::getAge, Arrays.asList(30,31,34,35));
        return this.list(queryWrapper);
    }

    @Override
    public List<User> eg9() {
        //9、只返回满足条件的其中一条语句即可
        //limit 1
        LambdaQueryWrapper<User> queryWrapper = Wrappers.<User>lambdaQuery()
                .last("limit 1");
        return this.list(queryWrapper);
    }

    @Override
    public List<User> eg10() {
        //10、名字中包含雨并且年龄小于40(需求1加强版)
        //第一种情况：select id,name
        //	           from user
        //	           where name like '%雨%' and age<40
        LambdaQueryWrapper<User> queryWrapper = Wrappers.<User>lambdaQuery().like(User::getName, "雨")
                .lt(User::getAge, 40).select(User::getId,User::getName);
        return this.list(queryWrapper);
    }

    @Override
    public List<User> eg11() {
        // 第二种情况：select id,name,age,email
        //	           from user
        //	           where name like '%雨%' and age<40
        // 排除字段法
        LambdaQueryWrapper<User> queryWrapper = Wrappers.<User>lambdaQuery().like(User::getName, "雨")
                .lt(User::getAge, 40).select(User.class,tableFieldInfo -> !tableFieldInfo.getColumn().equals("manager_id")
                        && !tableFieldInfo.getColumn().equals("create_time"));
        return this.list(queryWrapper);
    }

    @Override
    public List<User> eg12() {
        LambdaQueryWrapper<User> queryWrapper = Wrappers.<User>lambdaQuery().lt(User::getAge, "40");
        // 与原来的条件共存
        User user = new User();
        user.setName("刘红雨");
        //默认是等于，可以在entity对应字段上@TableField配置condition属性指定运算符号,condition = "%s&gt;#{%s}" 大于
        user.setAge(30);
        queryWrapper.setEntity(user);
        return this.list(queryWrapper);
    }

    @Override
    public List<User> eg13() {
        Map<SFunction<User,?>,Object> param = new HashMap(16);
        param.put(User::getName, "刘红雨");
        param.put(User::getAge, null);
        LambdaQueryWrapper<User> queryWrapper = Wrappers.<User>lambdaQuery().allEq(param);
       return this.list(queryWrapper);
    }

    @Override
    public List<Map<String,Object>> eg14() {
        //11、按照直属上级分组，查询每组的平均年龄、最大年龄、最小年龄。
        //并且只取年龄总和小于500的组。
        //select avg(age) avg_age,min(age) min_age,max(age) max_age
        //from user
        //group by manager_id
        //having sum(age) <500
        QueryWrapper<User> queryWrapper = Wrappers.<User>query()
                .select("avg(age) avg_age","min(age) min_age","max(age) max_age")
                .groupBy("manager_id")
                .having("sum(age) <{0}",500);
        return this.listMaps(queryWrapper);
        //[{max_age=40, avg_age=40.0000, min_age=40}, {max_age=25, avg_age=25.0000, min_age=25}, {max_age=32, avg_age=30.3333, min_age=28}]
    }

    @Override
    public List<Object> eg15() {
        LambdaQueryWrapper<User> queryWrapper = Wrappers.<User>lambdaQuery().like(User::getName, "雨").lt(User::getAge, 40);
        return this.listObjs(queryWrapper);
        //[1094590409767661570, 1094592041087729666]
    }

    @Override
    public Integer eg16() {
        LambdaQueryWrapper<User> queryWrapper = Wrappers.<User>lambdaQuery().like(User::getName, "雨").lt(User::getAge, 40);
        return this.count(queryWrapper);
    }

    @Override
    public List<User> eg17() {
        LambdaQueryWrapper<User> queryWrapper = Wrappers.<User>lambdaQuery().like(User::getName, "雨").lt(User::getAge, 40);
        return userDao.selectAll(queryWrapper);
    }
}
